﻿<%@ WebHandler Language="C#" Class="ZSProcessHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
using NPOI.SS.UserModel;
using System.IO;
using DatabaseAccess.DBUtility;
/// <summary>
/// add by longb 2022-07-25
/// 装试工艺相关处理
/// </summary>
public class ZSProcessHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    ProcessMaintainBusiness m_process = new ProcessMaintainBusiness();
    WorkStationBusiness wsBill = new WorkStationBusiness();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    #region 页面初始化
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "GetStepData"://获取工序列表数据
                    GetStepDatas(context);
                    break;
                case "SaveProcessMaintain"://保存工序基础数据（班组、装配周期、并行工序组）
                    SaveProcessMaintain(context);
                    break;
                case "GetWorkcenterByFactory"://查询工厂下-工区
                    GetWorkcenterByFactory(context);
                    break;
                case "GetShiftByWorkcenter"://查询工区下--组
                    GetShiftByWorkcenter(context);
                    break;
                case "GetWorkStationByShift"://查询工区下--班
                    GetWorkStationByShift(context);
                    break;
                case "GetTaskOrderData"://查询工艺装备任务(分页)
                    GetTaskOrderData(context);
                    break;
                case "UpdateProcessTaskOrderStatus"://更新--工艺装备任务
                    UpdateProcessTaskOrderStatus(context);
                    break;
                case "ImportWorkFlowStepTime"://上传工艺工时
                    ImportWorkFlowStepTime(context);
                    break;
                case "WorkFlowCopy"://工艺工时复制
                    WorkFlowCopy(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";

            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    #endregion


    #region MyRegion
    protected void GetStepDatas(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "查询完成" };
        DataSet ds = new DataSet();
        string workflowId = context.Request["workflowId"].ToString();
        if (string.IsNullOrWhiteSpace(workflowId))
        {
            result.Result = false;
            result.Message = "没有工艺路线";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        var para = new Dictionary<string, string>();
        para.Add("workflowId", workflowId);
        DataTable dttt = m_process.GetStepDatas(para);
        foreach (DataRow row in dttt.Rows)
        {
            row["WORKSTATIONNAME"] = m_process.GetWorkStationNameByID(row["WORKSTATIONIDS"].ToString());
        }
        result.DBTable = dttt;
        DataView dv = result.DBTable.DefaultView;
        DataTable dtt = dv.ToTable("parallelgroup", true, "PARALLELGROUPNAME");
        result.ReturnData = dtt;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 保存工序基础数据（班组、装配周期、并行工序组）
    /// </summary>
    public void SaveProcessMaintain(HttpContext context)
    {
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        uMESProcessResult result = new uMESProcessResult();
        result.Result = m_process.SaveProcessMaintain(dt);
        result.Message = result.Result ? "保存成功" : "保存失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    public void GetWorkcenterByFactory(HttpContext context)
    {
        var wcdt = commonBusiness.GetWorkCenterByFacoryID(m_UserInfo.FactoryID.ToString(), true);
        context.Response.Write(JsonConvert.SerializeObject(wcdt));
    }

    /// <summary>
    /// 获取班
    /// </summary>
    /// <param name="context"></param>
    public void GetShiftByWorkcenter(HttpContext context)
    {
        if (string.IsNullOrWhiteSpace(context.Request["workcenterId"].ToString()))
        {
            return;
        }
        var dt = commonBusiness.GetShiftInfoByWorkcenterID(context.Request["workcenterId"].ToString());
        var re = dt.DefaultView.ToTable(true, "SHIFTID", "SHIFTNAME");
        context.Response.Write(JsonConvert.SerializeObject(re));
    }
    /// <summary>
    /// 获取组下--工位信息
    /// </summary>
    /// <param name="context"></param>
    public void GetWorkStationByShift(HttpContext context)
    {
        if (string.IsNullOrWhiteSpace(context.Request["ShiftID"].ToString()))
        {
            return;
        }
        var para = new Dictionary<string, string>();
        para.Add("factoryId", m_UserInfo.FactoryID.ToString());
        para.Add("shiftId", context.Request["ShiftID"].ToString());
        var re = wsBill.GetWorkStation(para);
        context.Response.Write(JsonConvert.SerializeObject(re));
    }
    /// <summary>
    /// 查询工艺装备任务
    /// </summary>
    /// <param name="context"></param>
    private void GetTaskOrderData(HttpContext context)
    {
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var Condition = new Dictionary<string, string>();
        Condition.Add("ContainerNo", dt.Rows[0]["ContainerNo"].ToString());
        Condition.Add("Machine", dt.Rows[0]["Machine"].ToString());
        Condition.Add("MfgOrdername", dt.Rows[0]["MfgOrdername"].ToString());
        Condition.Add("WorkFlowType", dt.Rows[0]["WorkFlowType"].ToString());
        Condition.Add("HasBom", dt.Rows[0]["HasBom"].ToString());
        //Condition.Add("PartType", PartType);
        Condition.Add("PageSize", pageSize);
        Condition.Add("CurrentPageIndex", pageNumber);
        uMESProcessResult result = m_process.GetProcessTaskOrder(Condition);
        if (result.Result) result.Message = "查询成功";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 更新--工艺装备任务
    /// </summary>
    /// <param name="context"></param>
    private void UpdateProcessTaskOrderStatus(HttpContext context)
    {
        var para = new Dictionary<string, string>();
        para.Add("status", context.Request["status"].ToString());
        para.Add("enginepartplanID", context.Request["ids"].ToString());
        uMESProcessResult result = new uMESProcessResult();
        result.Result = m_process.UpdateProcessTaskOrderStatus(para);
        var str = context.Request["status"].ToString() == "1" ? "任务接收" : "准备完成";
        result.Message = result.Result ? str + "成功" : str + "失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="context"></param>
    private void ImportWorkFlowStepTime(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }

            if (!System.IO.Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = System.IO.Path.GetExtension(file.FileName);
            string fileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除
            IWorkbook Workbook = GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析出错", Result = false }));
                return;
            }
            DeleteFile(fpath);
            ISheet sheet = Workbook.GetSheetAt(0);
            uMESProcessResult result = GetWorkFlowStepData(sheet, IsXls);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    /// <summary>
    /// 获取工作薄对象
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public IWorkbook GetWorkbookObj(string filePath)
    {
        FileStream fileStream = null;
        try
        {
            string IsXls = Path.GetExtension(filePath);
            //读取文件流
            fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            fileStream.Position = 0;
            IWorkbook Workbook = null;
            switch (IsXls)
            {
                case ".xlsx":
                    Workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fileStream);
                    break;
                case ".xls":
                    Workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fileStream);
                    break;
                default:
                    break;
            }
            fileStream.Close();//关闭文件流
            return Workbook;
        }
        catch (Exception e)
        {
            fileStream.Close();//关闭文件流
            return null;
        }
    }
    /// <summary>
    /// 删除文件
    /// </summary>
    /// <param name="fpath">文件地址</param>
    private void DeleteFile(string fpath)
    {
        if (File.Exists(fpath))
        {
            new FileInfo(fpath).Attributes = FileAttributes.Normal;
            File.Delete(fpath);
        }
    }

    /// <summary>
    /// 处理Excle单元格数据
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="IsXls"></param>
    /// <returns></returns>
    private uMESProcessResult GetWorkFlowStepData(ISheet sheet, string xlsType)
    {
        DataTable Errordt = new DataTable();//格式错误行数据集
        Errordt.Columns.Add("productname");
        Errordt.Columns.Add("Error");
        DataTable wsdt = new DataTable();//筛选无误数据集
        wsdt.Columns.Add("WORKFLOWSTEPID");
        wsdt.Columns.Add("PREPARECYCLE");
        wsdt.Columns.Add("MAKECYCLE");
        wsdt.Columns.Add("TRANSFERCYCLE");
        wsdt.Columns.Add("PARALLELGROUPNAME");
        wsdt.Columns.Add("WORKSTATIONIDS");
        wsdt.Columns.Add("CHANGEOVERCYCLE");
        wsdt.Columns.Add("isbottleneck");
        uMESProcessResult result = new uMESProcessResult();
        int i = 0;
        IRow row = null;
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
        rows.MoveNext();
        while (rows.MoveNext())
        {
            switch (xlsType)
            {
                case ".xlsx":
                    row = (NPOI.XSSF.UserModel.XSSFRow)rows.Current;
                    break;
                case ".xls":
                    row = (NPOI.HSSF.UserModel.HSSFRow)rows.Current;
                    break;
            }
            if (row.Cells.Count == 0)//如果单元格为空，结束
            {
                break;
            }
            
            if (string.IsNullOrWhiteSpace(GetCellValue(row.GetCell(0))))
            {
                break;
            }
            var heardStr = string.Empty;
            if (i == 0)
            {
                if (GetCellValue(row.GetCell(0)) != "件号")
                {
                    heardStr += "第2行第A列不是【件号】,";
                }
                if (GetCellValue(row.GetCell(1)) != "件号版本")
                {
                    heardStr += "第2行第B列不是【件号版本】,";
                }
                if (GetCellValue(row.GetCell(2)) != "工艺路线及版本")
                {
                    heardStr += "第2行第C列不是【工艺路线及版本】,";
                }
                if (GetCellValue(row.GetCell(3)) != "工序号")
                {
                    heardStr += "第2行第D列不是【工序号】,";
                }
                if (GetCellValue(row.GetCell(4)) != "工艺名称")
                {
                    heardStr += "第2行第E列不是【工艺名称】,";
                }
                if (GetCellValue(row.GetCell(7)) != "工位")
                {
                    heardStr += "第2行第H列不是【工位】,";
                }
                if (GetCellValue(row.GetCell(8)) != "工序准备时间")
                {
                    heardStr += "第2行第I列不是【工序准备时间】,";
                }
                if (GetCellValue(row.GetCell(9)) != "工序装配时间")
                {
                    heardStr += "第2行第J列不是【工序装配时间】,";
                }
                if (GetCellValue(row.GetCell(10)) != "工序间转运时间")
                {
                    heardStr += "第2行第K列不是【工序间转运时间】,";
                }
                if (GetCellValue(row.GetCell(11)) != "工序换型时间")
                {
                    heardStr += "第2行第L列不是【工序换型时间】,";
                }
                if (GetCellValue(row.GetCell(12)) != "平行工序组")
                {
                    heardStr += "第2行第M列不是【平行工序组】,";
                }
            }
            if (!string.IsNullOrWhiteSpace(heardStr))
            {
                result.Message = heardStr.TrimEnd(',');
                break;
            }

            if (GetCellValue(row.GetCell(0)) == "件号")
            {
                i++;
                continue;
            }
            var productName = GetCellValue(row.GetCell(0));//件号
            var productVersion = GetCellValue(row.GetCell(1));//件号版本
            var workFlowNameVer = GetCellValue(row.GetCell(2));//工艺路线及版本
            var workFlowStepNo = GetCellValue(row.GetCell(3));//工序号
            var workFlowStepName = GetCellValue(row.GetCell(4));//工序名称

            var workStations = GetCellValue(row.GetCell(7));//工位
            var prepareCycle= GetCellValue(row.GetCell(8));//工序准备时间
            var markCycle = GetCellValue(row.GetCell(9));//工序装配时间
            var transferCycle= GetCellValue(row.GetCell(10));//工序间转运时间
            var changeoverCycle = GetCellValue(row.GetCell(11));//工序换型时间
            var parallelGroupName= GetCellValue(row.GetCell(12));//平行工序组
            var isbottleneck = GetCellValue(row.GetCell(13));//是否瓶颈工序
            var Dic = new Dictionary<string, string>();
            Dic.Add("PRODUCTNAME", productName);
            Dic.Add("PRODUCTREVISION", productVersion);
            DataTable dt = GetproductWorkFlowAndStep(Dic);
            var errStr = string.Empty;
            if (dt.Rows.Count==0)
            {
                errStr += "【件号/件号版本】不存在,";
            }
            Dic.Add("workFlowNameVer", workFlowNameVer);
            dt = GetproductWorkFlowAndStep(Dic);
            if (dt.Rows.Count == 0)
            {
                errStr += "【件号对应工艺路线及版本】不存在,";
            }
            Dic.Add("WORKFLOWSTEPNAME", workFlowStepNo + "-" + workFlowStepName);
            dt = GetproductWorkFlowAndStep(Dic);
            string workflowstepid = string.Empty;
            if (dt.Rows.Count == 0)
            {
                errStr += "【件号对应工序：" + workFlowStepNo + "-" + workFlowStepName + "】不存在,";
            }
            else
            {
                workflowstepid = dt.Rows[0]["workflowstepid"].ToString();
            }

            string workStationIDS = GetWorkStationIds(workStations);
            if (string.IsNullOrWhiteSpace(workStationIDS)) {
                errStr += "【件号:" + productName + "工位：" + workStations + "】不存在,";
            }
            if (!string.IsNullOrWhiteSpace(errStr))
            {
                DataRow Rrow = Errordt.NewRow();
                Rrow["productname"] = productName;
                Rrow["Error"] = "第" + (i + 1) + "条数据" + errStr.TrimEnd(',');
                Errordt.Rows.Add(Rrow);
            }
            else
            {
                DataRow Rrow = wsdt.NewRow();
                Rrow["WORKFLOWSTEPID"] = workflowstepid;
                Rrow["PREPARECYCLE"] = string.IsNullOrWhiteSpace(prepareCycle) ? "0" : (Convert.ToDecimal(prepareCycle)*60).ToString();
                Rrow["MAKECYCLE"] = string.IsNullOrWhiteSpace(markCycle) ? "0" : (Convert.ToDecimal(markCycle) * 60).ToString();
                Rrow["TRANSFERCYCLE"] = string.IsNullOrWhiteSpace(transferCycle) ? "0" : (Convert.ToDecimal(transferCycle) * 60).ToString();
                Rrow["CHANGEOVERCYCLE"] = string.IsNullOrWhiteSpace(changeoverCycle) ? (Convert.ToDecimal(Rrow["PREPARECYCLE"]) + Convert.ToDecimal(Rrow["TRANSFERCYCLE"])).ToString() : (Convert.ToDecimal(changeoverCycle) * 60).ToString(); 
                Rrow["PARALLELGROUPNAME"] = parallelGroupName;
                Rrow["WORKSTATIONIDS"] = workStationIDS;
                Rrow["isbottleneck"] = isbottleneck;
                wsdt.Rows.Add(Rrow);
            }
            i++;
        }
        if (wsdt.Rows.Count > 0)
        {
            result.Result = m_process.SaveProcessMaintain(wsdt);
            result.Message = result.Result ? "导入成功" + wsdt.Rows.Count + "条数据" + (Errordt.Rows.Count > 0 ? ";导入失败" + Errordt.Rows.Count + "条数据" : "") : "导入失败";
        }
        result.ReturnData = Errordt;
        return result;
    }

    /// <summary>
    /// 获取单元格值
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private string GetCellValue(ICell cell)
    {
        try
        {
            if (cell == null)
            {
                return "";
            }
            if (cell.CellType == CellType.Formula)//判断单元格是否有公式
            {
                cell.SetCellType(CellType.String);
            }
            if (cell.CellType == CellType.Numeric)//
            {
                if (DateUtil.IsCellDateFormatted(cell))
                {//判断单元是日期格式，特殊处理
                    var c = DateTime.FromOADate(cell.NumericCellValue);
                    return c.ToString("yyyy/MM/dd HH:mm:ss");
                }
            }
            if(cell.IsMergedCell){
                for (var i = 0; i < cell.Sheet.NumMergedRegions;i++ ) {
                    var cellrange = cell.Sheet.GetMergedRegion(i);
                    if (cell.ColumnIndex >= cellrange.FirstColumn&&cell.ColumnIndex<=cellrange.LastColumn&&cell.RowIndex>=cellrange.FirstRow&&cell.RowIndex<=cellrange.LastRow) {
                        return cell.Sheet.GetRow(cellrange.FirstRow).GetCell(cellrange.FirstColumn).ToString();
                    }
                }
            }
            return cell.ToString().Trim();
        }
        catch (Exception e)
        {
            return "";
        }
    }

    public DataTable GetproductWorkFlowAndStep(Dictionary<string, string> para)
    {
        var strSql = @"SELECT p2.WORKFLOWBASEID,p2.WORKFLOWID,p2.PRODUCTREVISION,p.PRODUCTNAME,w.WORKFLOWREVISION,w2.WORKFLOWNAME
                    ,w3.WORKFLOWSTEPNAME,w3.WORKFLOWSTEPID ,w3.PREPARECYCLE ,w3.MAKECYCLE ,w3.TRANSFERCYCLE,w3.CHANGEOVERCYCLE ,w3.WORKSTATIONIDS ,w3.PARALLELGROUPNAME,W3.isbottleneck
                    FROM PRODUCTBASE p 
                    LEFT JOIN PRODUCT p2 ON p2.PRODUCTBASEID =p.PRODUCTBASEID 
                    LEFT JOIN productworkflowmapping pw on pw.productid=p2.productid
                    LEFT JOIN WORKFLOW w ON w.WORKFLOWID =pw.workflowid
                    LEFT JOIN WORKFLOWBASE w2 ON w2.WORKFLOWBASEID =w.WORKFLOWBASEID
                    LEFT JOIN WORKFLOWSTEP w3 ON w3.WORKFLOWID =w.WORKFLOWID
                    WHERE 1=1";

        if (para.ContainsKey("PRODUCTNAME"))
        {
            strSql += " AND p.PRODUCTNAME='" + para["PRODUCTNAME"] + "'";
        }
        if (para.ContainsKey("PRODUCTREVISION"))
        {
            strSql += " AND p2.PRODUCTREVISION='" + para["PRODUCTREVISION"] + "'";
        }
        if (para.ContainsKey("workFlowNameVer"))
        {
            strSql += " AND w2.WORKFLOWNAME||':'||w.WORKFLOWREVISION='" + para["workFlowNameVer"] + "'";
        }
        if (para.ContainsKey("WORKFLOWSTEPNAME"))
        {
            strSql += " AND w3.WORKFLOWSTEPNAME='" + para["WORKFLOWSTEPNAME"] + "'";
        }
        if (para.ContainsKey("IDS"))
        {
            strSql += " AND w3.WORKFLOWID='" + para["IDS"] + "'";
        }
        return DatabaseHelper.Query_ReturnDatTable(strSql);
    }

    private string GetWorkStationIds(string WORKSTATIONNAMES) 
    {
        string SqlStr = @"SELECT  LISTAGG(w.WORKSTATIONID,',') WITHIN GROUP (ORDER BY w.WORKSTATIONNAME) WORKSTATIONIDS 
                        FROM WORKSTATION w WHERE w.WORKSTATIONNAME IN('" + WORKSTATIONNAMES .Replace(",","','")+ "')";

        string WorkStationIds = string.Empty;
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(SqlStr);
        if (dt.Rows.Count>0) {
            WorkStationIds = dt.Rows[0]["WORKSTATIONIDS"].ToString();
        }
        return WorkStationIds;
    }

    private void WorkFlowCopy(HttpContext context) 
    {
       string copyID = context.Request["copyID"].ToString();
       string workflowId = context.Request["workflowId"].ToString();
       string productName = context.Request["productName"].ToString();
       var Dic=new Dictionary<string,string>();
       if (!string.IsNullOrWhiteSpace(productName)) { Dic.Add("PRODUCTNAME", productName.Split(':')[0]); }
       Dic.Add("IDS", copyID);
       DataTable cdt = GetproductWorkFlowAndStep(Dic);
       Dic["IDS"] = workflowId;
       DataTable Wdt = GetproductWorkFlowAndStep(Dic);
       var c = 0;
       foreach (DataRow row in cdt.Rows) {
           DataRow[] Nrow = Wdt.Select("WORKFLOWSTEPNAME='" + row["WORKFLOWSTEPNAME"] + "'");
           if (Nrow.Length>0) {
               row["PREPARECYCLE"] = Nrow[0]["PREPARECYCLE"];
               row["MAKECYCLE"] = Nrow[0]["MAKECYCLE"];
               row["TRANSFERCYCLE"] = Nrow[0]["TRANSFERCYCLE"];
               row["CHANGEOVERCYCLE"] = Nrow[0]["CHANGEOVERCYCLE"];
               row["WORKSTATIONIDS"] = Nrow[0]["WORKSTATIONIDS"];
               row["PARALLELGROUPNAME"] = Nrow[0]["PARALLELGROUPNAME"];
               row["isbottleneck"] = Nrow[0]["isbottleneck"];
               c++;
           }
       } 
       uMESProcessResult result = new uMESProcessResult();
        if(c>0){
            result.Result = m_process.SaveProcessMaintain(cdt);
        }
       result.Message = result.Result ? "复制成功："+c+"条数据" : "保存失败";
       context.Response.Write(JsonConvert.SerializeObject(result));
    }
    #endregion
}